﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using eStock.SQL;

namespace eStock.DataObjects
{
    public class ComponentTypeCollection : DataCollection<ComponentType>
    {

        public override ComponentType GetById(int id)
        {
            return List.Cast<ComponentType>().FirstOrDefault(c => c.Id == id);
        }

        public override void Replace(ComponentType newValue)
        {
            List[IndexOf(GetById(newValue.Id))] = newValue;
        }

        public override bool Update()
        {
            try
            {
                SQLDataReader res;
                foreach (ComponentType c in List)
                {
                    res = MainWindow.Database.QueryRead("SELECT * FROM es_componentType WHERE id = " + c.Id);
                    int i = MainWindow.Database.FetchCount(res);
                    if (i == 0)
                    {
                        ((ComponentType)List[List.IndexOf(c)]).Id = Convert.ToInt32(MainWindow.Database.QueryWrite("INSERT INTO es_componentType (imgId, title, categoryId, serialNr, Pins, description, smd) VALUES " +
                                                                                                     "('" + c.ImgId + "', '" + MainWindow.Database.EscapeString(c.Title) + "', '" + c.CategoryId + "', '" + MainWindow.Database.EscapeString(c.SerialNr) + "', '" + c.Pins + "', '" + MainWindow.Database.EscapeString(c.Description) + "', '" + c.Smd + "');"));
                    }
                    else
                    {
                        MainWindow.Database.QueryWrite("UPDATE es_componentType SET imgId = '" + c.ImgId + "', title = '" + MainWindow.Database.EscapeString(c.Title) + "', categoryId = '" + c.CategoryId + "', serialNr = '" + MainWindow.Database.EscapeString(c.SerialNr) + "', Pins = '" + c.Pins + "', description = '" + MainWindow.Database.EscapeString(c.Description) + "', smd = '" + c.Smd + "' WHERE id = " + c.Id + ";");
                    }
                }

                res = MainWindow.Database.QueryRead("SELECT * FROM es_componentType");
                Dictionary<string, object> data = MainWindow.Database.FetchArray(res);
                List<int> removeIds = new List<int>();
                while (data != null)
                {
                    ComponentType c = GetById(Convert.ToInt32(data["id"]));
                    if (c == null)
                    {
                        removeIds.Add(Convert.ToInt32(data["id"]));
                    }
                    data = MainWindow.Database.FetchArray(res);
                }
                if (removeIds.Count > 0)
                {
                    string sqlString = "DELETE FROM es_componentType WHERE ";
                    for (int j = 0; j < removeIds.Count; j++)
                    {
                        if (j != 0)
                            sqlString += " AND ";
                        sqlString += "id = " + removeIds[j];
                    }
                    MainWindow.Database.QueryWrite(sqlString);
                }

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
